<?php
/**
 * get table comments like array(tablename => array( comment ) )
 */
if (! function_exists ( 'GetTableInfo' )) {
	function GetTableInfo() {
		global $hostname, $username, $password, $database;
		
		$mysqli = new mysqli ( $hostname, $username, $password, 'information_schema' );
		if (mysqli_connect_errno ()) {
			exit ( "Connect failed: %s\n" . mysqli_connect_error () );
		}
		
		$sql = "select table_name,table_comment from tables where table_schema = '$database'";
		$mysqli->set_charset('utf8');
		$result = $mysqli->query ( $sql );
		while ( $r = $result->fetch_row () ) {
			$r [1] = empty ( $r [1] ) ? $r [0] : $r [1];
			$fields [$r [0]] = array ($r [1] );
		}
		
		$result->close ();
		$mysqli->close ();
		
		return $fields;
	}
}

/**
 * get table comments 
 * @param string $table
 */

if (! function_exists ( 'GetTableComment' )) {
	function GetTableComment($table) {
		$info = GetTableInfo ();
		return $info [$table] [0];
	}
}

/**
 * get field's informations like array(fieldname => array( datatype, comment ) )
 * @param string $table
 */
if (! function_exists ( 'GetFieldsInfoByTable' )) {
	function GetFieldsInfoByTable($table) {
		global $hostname, $username, $password, $database;
		
		$mysqli = new mysqli ( $hostname, $username, $password, 'information_schema' );
		if (mysqli_connect_errno ()) {
			exit ( "Connect failed: %s\n" . mysqli_connect_error () );
		}
		
		$sql = "select COLUMN_NAME , COLUMN_COMMENT , DATA_TYPE  from COLUMNS where table_schema = '$database' and table_name='$table'";
		$mysqli->set_charset('utf8');
		$result = $mysqli->query ( $sql );
		while ( $r = $result->fetch_row () ) {
			$r [1] = empty ( $r [1] ) ? $r [0] : $r [1];
			$fields [$r [0]] = array ($r [1], $r [2] );
		}
		
		$result->close ();
		$mysqli->close ();
		
		return $fields;
	}
}

/**
 * get field's informations like array(fieldname => array( datatype, comment ) )
 * @param string $table
 * @param mixd $field
 */
if (! function_exists ( 'GetFieldInfo' )) {
	function GetFieldInfo($table, $field) {
		$info = GetFieldsInfoByTable ( $table );
		if (is_string ( $field ) && array_key_exists ( $field, $info )) {
			return $info [$field];
		} elseif (is_array ( $field )) {
			return array_intersect_key ( $info, $field );
		} else {
			exit ( __FILE__ . __FUNCTION__ );
		}
	}
}

/**
 * change array keys to comment like array( comment => value )
 * @param array $array
 * @param string $table
 */
if (! function_exists ( 'ChangeKeyToComment' )) {
	function ChangeKeyToComment($table, $array) {
		$t = GetFieldInfo ( $table, $array );
		foreach ( $array as $k => $v ) {
			$r [$t [$k] [0]] = $array [$k];
		}
		return $r;
	}
}

/**
 * get comments like array( key => comment )
 * @param string $table
 * @param array $field
 */
if (! function_exists ( 'GetFieldComment' )) {
	function GetFieldComment($table, $field) {
		$info = GetFieldsInfoByTable ( $table );
		if (is_string ( $field ) && array_key_exists ( $field, $info )) {
			return $info [$field];
		} elseif (is_array ( $field )) {
			foreach ( $field as $k => $v ) {
				$r [$k] = $info [$k] [0];
			}
			return $r;
		} else {
			exit ( __FILE__ . __FUNCTION__ );
		}
	}
}

/**
 * format arrays to an array as array( Name => array(Name，名称，类型，值，单位) )
 * @param $key as array( Name => array ( comment, datatype ) )
 * @param $aValue as array( Name => value )
 * @param $aUnit as array( Name => value )
 * @param $aRelation as array( field=>array(flag{V/A/X}, otable[, ofield])
 */
if (! function_exists ( 'FormatArrayToUI' )) {
	function FormatArrayToUI($keyinfo, $aUnit = array(), $aValue = array(), $aRelation = array()) {
		foreach ( $keyinfo as $k => $v ) {
			$key=$k;
			$comm=$v[0];
			$type=$v[1];
			$value=isset ( $aValue [$key] )? $aValue [$key] : '';
			$unit=isset ( $aUnit [$key] ) ? $aUnit [$key] : '';
						
			// handle relations between the tables
			$k=substr(strstr($key,'_'),1);
			if (array_key_exists($k,$aRelation)) {
				$stable=str_replace("_$k",'',$key);
				$rltn=$aRelation[$k];
				$otable=$rltn[1];
				$tpre=$otable.'_';
				switch ($rltn[0]){
					case 'V':
						$info=array();
						$tmp=choice("select {$tpre}id, {$tpre}name from $otable");
						foreach ($tmp as $v){
							if ($v[$tpre.'id']==$value){
								$info['@'.$v[$tpre.'id']]=$v[$tpre.'name'];
							}else{
								$info[$v[$tpre.'id']]=$v[$tpre.'name'];
							}
						}
						$type='select';
						$value=$info;
						break;
					case 'A(p)':
						$info=array();
						$tmp=choice("select {$tpre}id, {$tpre}name from $otable");
						$rel=isset($aValue[$stable.'_id'])?GetValuesByKey(choice("select {$tpre}id from $otable where {$tpre}{$rltn[2]}={$aValue[$stable.'_id']}"),"{$tpre}id"):array();
						foreach ($tmp as $v){
							if (in_array($v[$tpre.'id'],$rel)){
								$info['@'.$v[$tpre.'id']]=$v[$tpre.'name'];
							}else{
								$info[$v[$tpre.'id']]=$v[$tpre.'name'];
							}
						}
						$type='list';
						$value=$info;
						break;
					case 'X':
						$rpre='relation_';
						$info=array();
						$tmp=choice("select {$tpre}id, {$tpre}name from $otable");
						if (isset($aValue[$stable.'_id'])){
							$rel1=GetValuesByKey(choice("select distinct {$rpre}r1 sid from relation where {$rpre}t2='{$stable}' and {$rpre}t1='{$otable}' and {$rpre}r2={$aValue[$stable.'_id']}"),'sid');
							$rel2=GetValuesByKey(choice("select distinct {$rpre}r2 sid from relation where {$rpre}t1='{$stable}' and {$rpre}t2='{$otable}' and {$rpre}r1={$aValue[$stable.'_id']}"),'sid');
							$rel=array_unique(array_merge($rel1,$rel2));
						}else{
							$rel=array();
						}
						foreach ($tmp as $v){
							if (in_array($v[$tpre.'id'],$rel1)){
								$info['@'.$v[$tpre.'id']]=$v[$tpre.'name'];
							}else{
								$info[$v[$tpre.'id']]=$v[$tpre.'name'];
							}
						}
						$type='list';
						$value=$info;
						break;
					default :
						break;
				}
			}
			
			$r [$key] = array ($key, $comm, $type, $value, $unit );
		}
		return $r;
	}
}

if (! function_exists ( 'FormatListWithViewLink' )) {
	/**
	 * alimit is like array( name => array( flag[V,A,X], oTable, oField  ) )
	 * @param array $result
	 * @param array $alimit
	 * @param string $param
	 */
	function FormatListWithViewLink($result, $alink, $stable ) {
		if (! empty ( $alink )) {
			
			foreach ( $alink as $k => $v ) {
				$k=$stable.'_'.$k;				
				if ($v[0]=='V'){
					// batch to one
					$l=implode("', '",GetValuesByKey($result, $k));
					$oid=$v[1].'_id';
					$oname=$v[1].'_name';
					$sql="select $oid, $oname from $v[1] where $oid in ( '$l' )";
					$obj=TransArrayByIndex(choice($sql),$oid);
					foreach ( $result as $ki => $vi ) {
						if (!empty ( $vi[$k] ) && isset($obj[$vi[$k]]) && !empty($obj[$vi[$k]]))
							$result [$ki] [$k] = GetLink ( $v [1].'_action.php?act=v&id=' . $vi[$k] , $obj[$vi[$k]][$oname] );
					}
					
				}elseif ($v[0]=='A'){
					// one to batch
					$oid=$v[1].'_id';
					$sid=$stable.'_id';
					$v[2]="$v[1]_$v[2]";
					$l=implode("', '",GetValuesByKey($result, $sid));
					$sql="select $v[2], count($oid) num from $v[1] where $v[2] in ('$l') group by $v[2]";
					$obj=TransArrayByIndex(choice($sql),$v[2]);
					foreach ($result as $ki => $vi ) {
						if (!empty ( $vi[$k] ) && isset($obj[$vi[$sid]]) && !empty($obj[$vi[$sid]]['num']))
							$result [$ki] [$k] .= '&nbsp('.GetLink( "$v[1].php?$v[2]=$vi[$sid]&o=$v[2]&b=0", $obj[$vi[$sid]]['num']).')';
					}
					
				}elseif ($v[0]=='X'){
					// batch to batch
					$rid='relation_id';
					$rt1='relation_t1';
					$rt2='relation_t2';
					$rr1='relation_r1';
					$rr2='relation_r2';
					$sid=$stable.'_id';
					$v[2]="$v[1]_$v[2]";
					$l=implode("', '",GetValuesByKey($result, $sid));
					$sql1="select $rt1 st,$rt2 sr,$rr1 ot,count($rid) num from relation where $rt1='$stable' and $rt2='$v[1]' and $rr1 in ('$l') group by $rt1,$rt2,$rr1";
					$sql2="select $rt2 st,$rt1 sr,$rr2 ot,count($rid) num from relation where $rt1='$v[1]' and $rt2='$stable' and $rr2 in ('$l') group by $rt1,$rt2,$rr2";
					$obj1=choice($sql1);
					$obj2=choice($sql2);
					$obj=TransArray(array_merge($obj1,$obj2));
					// echo $sql1;MyEcho($obj1);echo $sql2;MyEcho($obj2);MyEcho($obj);
					foreach ($result as $ki => $vi ) {
						if (!empty ( $vi[$k] ) && isset($obj[$stable][$v[1]][$vi[$sid]]) && !empty($obj[$stable][$v[1]][$vi[$sid]]))
							$result [$ki] [$k] .= '&nbsp('.GetLink( "$v[1].php?i=$vi[$sid]&o=$v[2]&b=0&t=$stable", $obj[$stable][$v[1]][$vi[$sid]][0]).')'; 
					}
					
				}else{
					trigger_error('please check params',E_USER_NOTICE);
				}
			}
		}
		return $result;
	}
}

if (!function_exists('TransArrayByIndex')){
	function TransArrayByIndex($a,$index){
		$r=array();
		foreach ($a as $k => $v) {
			$r[$v[$index]]=array_diff_key($v,array_flip(array($index)));
		}
		return $r;
	}
}

if (!function_exists('GetInfoByRelation')){
	function GetInfoByRelation($table, $alink , $callback){
		$r=array();
		if (!empty($alink)){
			$rt='relation';
			foreach ($alink as $k => $v) {
				$sql="select * from $rt,$alink[0] where 
					(".$rt."_t1=$table and ".$rt."_t2=$v[0] and ) or ";;
			}
		}
	}
}

if (!function_exists('GetValuesByKey')){
	/**
	 * return the columnar values from the array decided by the key
	 * @param $array
	 * @param $key
	 */
	function GetValuesByKey($array,$key){
		$r=array();
		foreach ($array as $v) {
			$r[]=$v[$key];
		}
		return $r;
	}
}

if (!function_exists('TransArray')){
	function TransArray($a){
		$t=array();
		foreach ($a as $v) {
			if (is_array($v)){
				$t=array_merge_recursive($t,TransArray($v));
			}else{
				return Trans($a);
			}
		}
		return $t;
	}
}

if (!function_exists('Trans')){
	function Trans($a){
		$t=array_shift($a);
		if (empty($a)){
			return $t;
		}else {
			return array($t=>Trans($a));
		}
	}
}


